#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
电表遥测数据 CRUD
职责：
1. 仅封装 SQL，不做业务判断；
2. 统一返回 (total, List[Model])；
3. 任何数据库异常转 BizException，error_code 必须来自 ErrorCode 枚举。
"""

import json
from datetime import datetime
from decimal import Decimal
from typing import List, Optional, Tuple, Dict, Any

from app.models.telemetry_models import MeterTelemetry
from app.schemas.telemetry_schemas import TelemetryListRequest
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger

logger = create_logger(__name__)


# ---------- 私有工具 ----------
def _convert(row: Dict[str, Any]) -> Dict[str, Any]:
	"""把数据库原始行转换成 Python 原生类型，确保 Pydantic 校验通过"""
	# 1. Decimal -> float
	for col in ("voltage", "current", "watt", "total_kwh", "temperature1", "temperature2"):
		if isinstance(row.get(col), Decimal):
			row[col] = float(row[col])
	# 2. JSON 字符串 -> dict
	ext = row.get("ext_json")
	if ext and isinstance(ext, str):
		row["ext_json"] = json.loads(ext)
	elif ext is None:
		row["ext_json"] = None
	# 3. 主键转 int
	row["id"] = int(row["id"])
	return row


# ---------- 查询 ----------
async def get_telemetry_list(
		db: DatabaseService,
		query: TelemetryListRequest,
		page: int,
		size: int,
) -> Tuple[int, List[MeterTelemetry]]:
	"""
	分页获取遥测数据
	返回：(总条数, 模型列表)
	"""
	where_parts: List[str] = []
	params: List[Any] = []

	if query.meter_code:
		where_parts.append("meter_code = %s")
		params.append(query.meter_code)
	if query.serial_no:
		where_parts.append("meter_code = %s")
		params.append(query.serial_no)
	if query.start_time:
		where_parts.append("ts >= %s")
		params.append(query.start_time)
	if query.end_time:
		where_parts.append("ts <= %s")
		params.append(query.end_time)
	if query.quality:
		where_parts.append("quality = %s")
		params.append(query.quality)

	where_sql = " AND ".join(where_parts)
	if where_sql:
		where_sql = f"WHERE {where_sql}"

	# 1. 总数
	count_row = db.get_one(
		f"SELECT COUNT(*) AS cnt FROM dc_meter_telemetry {where_sql}", params
	)
	total = count_row["cnt"] if count_row else 0
	if total == 0:
		return 0, []

	# 2. 分页查询
	offset = (page - 1) * size
	rows = db.get_all(
		f"""
        SELECT id, msg_id, phase, meter_code, ts, voltage, current, watt,
               temperature1, temperature2, ext_json, total_kwh, quality, created_at
        FROM dc_meter_telemetry
        {where_sql}
        ORDER BY ts DESC
        LIMIT %s OFFSET %s
        """,
		params + [size, offset],
	)

	items = [MeterTelemetry(**_convert(r)) for r in rows]
	return total, items


# ---------- 按 ID 查询 ----------
async def get_telemetry_by_id(
		db: DatabaseService, telemetry_id: int
) -> Optional[MeterTelemetry]:
	sql = """
        SELECT id, msg_id, phase, meter_code, ts, voltage, current, watt,
               temperature1, temperature2, ext_json, total_kwh, quality, created_at
        FROM dc_meter_telemetry
        WHERE id = %s
    """
	try:
		row = db.get_one(sql, (telemetry_id,))
		return MeterTelemetry(**_convert(row)) if row else None
	except Exception as e:
		logger.exception("查询遥测数据失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR, message="查询遥测数据失败")


# ---------- 幂等写入 ----------
async def create_or_update_telemetry(
		db: DatabaseService,
		msg_id: str,
		phase: str,
		meter_code: str,
		ts: datetime,
		voltage: Optional[Decimal],
		current: Optional[Decimal],
		watt: Optional[Decimal],
		total_kwh: Optional[Decimal],
		temperature1: Optional[Decimal],
		temperature2: Optional[Decimal],
		ext_json: Optional[Dict[str, Any]],
		quality: str,
) -> int:
	"""
	利用唯一键 (msg_id, phase, ts) 做 UPSERT；
	返回自增 id（插入或已存在记录的 id）。
	"""
	sql = """
    INSERT INTO dc_meter_telemetry
        (msg_id, phase, meter_code, ts, voltage, current, watt, total_kwh,
         temperature1, temperature2, ext_json, quality)
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
    ON DUPLICATE KEY UPDATE
        voltage      = VALUES(voltage),
        current      = VALUES(current),
        watt         = VALUES(watt),
        total_kwh    = VALUES(total_kwh),
        temperature1 = VALUES(temperature1),
        temperature2 = VALUES(temperature2),
        ext_json     = VALUES(ext_json),
        quality      = VALUES(quality)
    """
	params = (
		msg_id, phase, meter_code, ts,
		voltage, current, watt, total_kwh,
		temperature1, temperature2,
		json.dumps(ext_json, ensure_ascii=False) if ext_json else None,
		quality,
	)
	try:
		db.insert(sql, params)
		# 取出 id
		row = db.get_one(
			"SELECT id FROM dc_meter_telemetry WHERE msg_id=%s AND phase=%s AND ts=%s",
			(msg_id, phase, ts),
		)
		return int(row["id"])
	except Exception as e:
		logger.exception("写入遥测数据失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message="写入遥测数据失败")


# ---------- 物理删除 ----------
async def delete_telemetry(db: DatabaseService, telemetry_id: int) -> bool:
	"""返回是否真删到数据"""
	sql = "DELETE FROM dc_meter_telemetry WHERE id = %s"
	try:
		return db.delete(sql, (telemetry_id,)) > 0
	except Exception as e:
		logger.exception("删除遥测数据失败: %s", e)
		raise BizException.from_error_code(ErrorCode.DATABASE_ERROR, message="删除遥测数据失败")
